package com.db.onlinedb.controller;

import com.db.onlinedb.entity.TreeNode;
import com.db.onlinedb.utils.Conn;
import com.db.onlinedb.utils.ConnectionMap;
import com.db.onlinedb.utils.LinkName;
import com.db.onlinedb.utils.MysqlConnectionPool;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@RestController
@CrossOrigin(origins = "*", maxAge = 3600)
public class LinkController {

    /**
     * mysql连接
     *
     * @param link     连接名
     * @param host     主机
     * @param port     端口
     * @param username 用户名
     * @param password 密码
     * @return treeNode
     */
    @PostMapping("/mysqlLink")
    public TreeNode link(String link, String host, String port, String username, String password) {
        // 通过连接池获取Conn
        Conn conn = MysqlConnectionPool.getConn();
        Connection connection = conn.getConnection(host, port, username, password);
        if (connection != null){
            // 存入全局映射中
            LinkName ln = new LinkName(link, host);
            ConnectionMap.addMap(link+host, conn);
            // 获取整个tree
            TreeNode root = new TreeNode();
            root.setLabel(link);
            root.setId("link");
            try {
                Statement stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery("show databases");
                List<TreeNode> nodeList = new ArrayList<>();
                // 加入所有数据库名称
                while (rs.next()) {
                    System.out.println(rs.getString(1));
                    TreeNode node = new TreeNode();
                    node.setId("database");
                    node.setLabel(rs.getString(1));
                    nodeList.add(node);
                }
                root.setChildren(nodeList);
                // 根据数据库名获取所有表名和视图名
                DatabaseMetaData databaseMetaData = connection.getMetaData();
                for (int i = 0; i < nodeList.size(); i++) {
                    List<TreeNode> l = new ArrayList<>();
                    // 添加数据路对应所有表
                    TreeNode node = new TreeNode();
                    node.setLabel("表");
                    node.setId("tableContainer");
                    List<TreeNode> list = new ArrayList<>();
                    rs = databaseMetaData.getTables(nodeList.get(i).getLabel(), null, "%", new String[]{"TABLE"});
                    while (rs.next()) {
                        TreeNode treeNode = new TreeNode();
                        treeNode.setId("table");
                        treeNode.setLabel(rs.getString("TABLE_NAME"));
                        treeNode.setChildren(new ArrayList<>());
                        list.add(treeNode);
                    }
                    node.setChildren(list);

                    // 添加数据路对应所有视图
                    TreeNode node1 = new TreeNode();
                    node1.setLabel("视图");
                    node1.setId("viewContainer");
                    List<TreeNode> list1 = new ArrayList<>();
                    rs = databaseMetaData.getTables(nodeList.get(i).getLabel(), null, "%", new String[]{"VIEW"});
                    while (rs.next()) {
                        TreeNode treeNode = new TreeNode();
                        treeNode.setId("view");
                        treeNode.setLabel(rs.getString("table_name"));
                        treeNode.setChildren(new ArrayList<>());
                        list1.add(treeNode);
                    }
                    node1.setChildren(list1);

                    l.add(node);
                    l.add(node1);
                    nodeList.get(i).setChildren(l);
                }
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
            return root;
        }
        return null;
    }
}
